﻿Imports System.Data.OleDb

Public Class Experience_Detail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


            thanks_for_rating.Visible = False


            'The following code populates the second Gridview (The one with the comments) every time the page loads.

            If Request.Params("ID") = "" Then
                Response.Redirect("Experiences_All.aspx")
            End If
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TravellingAloneConnectionString").ConnectionString)
            Dim SqlString As String = "SELECT * FROM experiences_comments WHERE experience_fkid = @f1"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", Request.Params("ID"))
            oleDbConn.Open()
            Dim dataReader = cmd.ExecuteReader()
            GridView2.DataSource = dataReader
            GridView2.DataBind()
            oleDbConn.Close()

    End Sub

    Protected Sub btn_submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_submit.Click

        'Clicking the Submit button updates the comments table, and reloads the same page.

        Dim redirect_param As String = Request.Params("ID").ToString
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TravellingAloneConnectionString").ConnectionString)
        Dim SqlString As String = "Insert into experiences_comments(the_author, the_comment, experience_fkid) Values (@f1, @f2, @f3)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", User.Identity.Name)
        cmd.Parameters.AddWithValue("@f2", tb_comment.Text)
        cmd.Parameters.AddWithValue("@f3", Request.Params("ID"))
        oleDbConn.Open()
        cmd.ExecuteNonQuery()
        oleDbConn.Close()
        Response.Redirect("Experience_Detail.aspx?ID=" & redirect_param)

    End Sub

    Protected Sub rating_submit_button_Click(ByVal sender As Object, ByVal e As EventArgs) Handles rating_submit_button.Click

        'This code handles saving user ratings in the database. Since a user can only have one rating per experience
        'corresponding to them, in case a user has already got a record in the table, the UPDATE statement is used to avoid creating
        'a new record. Otherwise we use INSERT to make a new record.


        'This part reads to check if the user has already rated this experience.

        Dim connection1 As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("TravellingAloneConnectionString").ConnectionString)
        Dim read_sql_string As String
        read_sql_string = "SELECT * FROM experiences_ratings WHERE rating_user = @f1"
        Dim command1 As New OleDb.OleDbCommand(read_sql_string, connection1)
        command1.CommandType = CommandType.Text
        command1.Parameters.AddWithValue("@f1", User.Identity.Name)
        connection1.Open()
        Dim rdr As OleDb.OleDbDataReader
        rdr = command1.ExecuteReader()

        'This takes place if there is a record from this user.

        If rdr.HasRows Then

            rdr.Close()

            Dim update_sql_string As String
            update_sql_string = "UPDATE experiences_ratings SET rating_value = @f8 WHERE (rating_user = @f9)"
            Dim command2 As New OleDb.OleDbCommand(update_sql_string, connection1)
            command2.CommandType = CommandType.Text
            command2.Parameters.AddWithValue("@f8", Rating1.CurrentRating)
            command2.Parameters.AddWithValue("@f9", User.Identity.Name)
            command2.ExecuteNonQuery()

        Else

            'In case there is no record from this user we create one.

            rdr.Close()

            Dim insert_sql_string As String
            insert_sql_string = "INSERT INTO experiences_ratings (experience_fkid, rating_user, rating_value) VALUES (@f4, @f5, @f6)"
            Dim command3 As New OleDb.OleDbCommand(insert_sql_string, connection1)
            command3.CommandType = CommandType.Text
            command3.Parameters.AddWithValue("@f4", Request.Params("ID"))
            command3.Parameters.AddWithValue("@f5", User.Identity.Name)
            command3.Parameters.AddWithValue("@f6", Rating1.CurrentRating)
            command3.ExecuteNonQuery()

        End If

        connection1.Close()

        thanks_for_rating.Visible = True

    End Sub

End Class